/*
 * AttrTable.java - SQL operations with the table 'attr'
 *  in the database 'context' (copy of WebDeso).
 *
 * Copyright (c) 2007 Computer-Aided Integrated Systems Laboratory, 
 * St. Petersburg Institute for Informatics and Automation RAS http://cais.lisa.iias.spb.su
 */

package dbcontext;

import dbindex.Language;

import java.sql.Statement;
import java.sql.ResultSet;
import java.sql.SQLException;

/** Routines to work with the table 'class' in the database 'context'.
 */
public class AttrTable {
   
    /** attribute's id in database */
    public int      attr_id;
    
    /** id of ontology that is compared against the document*/
    public int      onto_id;
    
    /** id of base ontology*/
    public int      base_onto_id;
    
    /** ??? 0 - simple, 1 - input, 2 - output - see database structure */
    public int      type;
    
    /** id, title (name), and description of class in the table 'term' */
    public TermTable term;
    
    /** obsolete, it was used in MAccess before MySQL */
    public int      session_id;
    
    
    /** Creates a new instance of AttrTable */
    public AttrTable() {
    }
    
    
    /** Inserts element to the table 'attr' in MySQL database.
     * 
     * SQL example:
     * INSERT INTO term (lang_id, text, description) VALUES (0, "title12", "desc12");
     * INSERT INTO attr (onto_id,baseonto_id,id,term_id,type) VALUES(101,102,41,12,1);<br>
     *
     * UPDATE attr SET onto_id=101,baseonto_id=102,term_id=12,type=1 WHERE id=41;<br>
     *
     *  @param attr_name   name of attr (stored in the table term)
     *  @param attr_desc   description of attr (stored in the table term)
     *
     * Attention: PRIMARY KEY attr.id is input paremeter attr_id, so the 
     * database 'context' depends on 'webdeso' now.
     */
    public static void insert ( java.sql.Connection conn, Language lang,
                                int attr_id,
                                int type,
                                int onto_id,  int base_onto_id,
                                String attr_name,String attr_desc) {
        
        Statement   s = null;
        ResultSet   rs= null;
        String      str_sql = null;
        try 
        {
            s = conn.createStatement ();
            
            int term_id = TermTable.insertAndGetID(conn, lang, attr_name, attr_desc);
            AttrTable a = get(conn, lang, onto_id, attr_name);
            if (a == null)
            {
                // IF there is no triple (lang,onto_id,attr_name) in tables 'term' and 'attr'
                //   if there is no record with attr_id in the table 'attr'
                //     then insert 
                //   else update WHERE attr_id 
                // ENDIF
                
                a = getByAttrID(conn, attr_id);
                if (a != null) 
                {   // UPDATE
	            str_sql = "UPDATE attr SET"     + 
                                    " onto_id="     + onto_id       + 
                                    ",baseonto_id=" + base_onto_id  + 
                                    ",type="        + type          +
                                    ",term_id = "   + term_id       + 
                              " WHERE id = "+ attr_id;
                } else {
	            str_sql = "INSERT INTO attr (onto_id,baseonto_id,id,term_id,type) "+ 
                                    "VALUES (" + onto_id + 
                                    ","+ base_onto_id + 
                                    ","+ attr_id + 
                                    ","+ term_id +
                                    ","+ type + ")";
                }
                s.executeUpdate (str_sql);
            }
        }catch(SQLException ex) {
            System.err.println("SQLException (context.ClassTable.java insert()):: sql='" + str_sql + "' " + ex.getMessage());
        } finally {
            if (rs != null) {   try { rs.close(); } catch (SQLException sqlEx) { }  rs = null; }
            if (s != null)  {   try { s.close();  } catch (SQLException sqlEx) { }  s = null;  }
        }
    }
    
    
    /** Gets ID of the attribute (by language, title and description of the term), 
     * if it is absent then creates it and returns ID. 
     */
    /*
    public static int insertAndGetID (java.sql.Connection conn,Language lang,
            int onto_id,int base_onto_id,
            String attr_name,String attr_desc,
            int type) 
    {
        AttrTable a = get (conn, lang, onto_id, attr_name);
        if(0 == a.attr_id) {
            insert(conn, lang, int attr_id, 
                   type, 
                   onto_id,   base_onto_id,
                   attr_name, attr_desc);
            
            a = get (conn, lang, onto_id, attr_name);
        }
        return a.attr_id;
    }
    */
    
    /** Selects (attr_id,parent_id,base_onto_id) from the table 'attr'
     * by the ontology id and attribute name.
     *
     *  SQL:
     *  SELECT id,type,baseonto_id FROM attr WHERE onto_id=3 AND term_id=13;
     *  
     *  @param onto_id      id of ontology
     *  @param attr_name   name of attribute title in the table 'term'
     *  @return AttrTable object with initialized fields: attr_id, type, base_onto_id, term
     */
    public static AttrTable get (java.sql.Connection conn,Language lang,int onto_id,String attr_name) {
        
        AttrTable cc = null;
        Statement   s = null;
        ResultSet   rs= null;
        String      str_sql = null;
        
        int term_id = TermTable.getID(conn, lang, attr_name);
        if(0 == term_id) {
            return null;
        }
        
        try {
            s = conn.createStatement ();
            str_sql = "SELECT id,type,baseonto_id FROM attr WHERE onto_id=" + onto_id + " AND term_id=" + term_id;
            s.executeQuery (str_sql);
            
            rs = s.getResultSet ();
            if (rs.next ())
            {
                cc = new AttrTable();
                cc.attr_id = rs.getInt("id");
                cc.type    = rs.getInt("type");
                cc.onto_id = onto_id;
                cc.base_onto_id = rs.getInt("baseonto_id");
                cc.term = TermTable.getByID(conn, term_id);
            }
        } catch(SQLException ex) {
            System.err.println("SQLException (Context.AttrTable.java get()):: sql='" + str_sql + "' " + ex.getMessage());
        } finally {
            if (rs != null) {   try { rs.close(); } catch (SQLException sqlEx) { }  rs = null; }
            if (s != null)  {   try { s.close();  } catch (SQLException sqlEx) { }  s = null;  }
        }
        
        return cc;
    }
    
    
    /** Selects (type,onto_id,base_onto_id,term_id) from the table 'attr'
     * by the attr id (UNIQUE PRIMARY KEY in the table).
     *
     *  SQL:
     *  SELECT type,onto_id,baseonto_id,term_id FROM attr WHERE id=3;
     *
     *  @param id     id of attribute in the table 'attr'
     *  @return AttrTable object with initialized fields: parent_id, onto_id, base_onto_id, term_id
     */
    public static AttrTable getByAttrID (java.sql.Connection conn,int attr_id) {
        AttrTable cc = null;
        
        int         onto_id = 0;
        Statement   s = null;
        ResultSet   rs= null;
        int         size = 0;
        String      str_sql = null;
        try {
            s = conn.createStatement ();
            str_sql = "SELECT type,onto_id,baseonto_id,term_id FROM attr WHERE id=" + attr_id;
            s.executeQuery (str_sql);
            
            rs = s.getResultSet ();
            if (rs.next ())
            {
                cc = new AttrTable();
                cc.attr_id = attr_id;
                cc.type    = rs.getInt("type");
                cc.onto_id      = rs.getInt("onto_id");
                cc.base_onto_id = rs.getInt("baseonto_id");
                cc.term         = TermTable.getByID(conn, rs.getInt("term_id"));
            }
        } catch(SQLException ex) {
            System.err.println("SQLException (Context.AttrTable.java get()):: sql='" + str_sql + "' " + ex.getMessage());
        } finally {
            if (rs != null) {   try { rs.close(); } catch (SQLException sqlEx) { }  rs = null; }
            if (s != null)  {   try { s.close();  } catch (SQLException sqlEx) { }  s = null;  }
        }
        
        return cc;
    }
    
    
    
    /** Deletes attr's row by attribute name and ontolody ID.
     *  SQL:        
     *  DELETE FROM attr WHERE onto_id=101 AND term_id=12;
     */
    public static void delete (java.sql.Connection conn,Language lang,int onto_id,String attr_name) {
        
        TermTable   term = null;
        Statement   s = null;
        ResultSet   rs= null;
        String      str_sql = null;
        
        int term_id = TermTable.getID(conn, lang, attr_name);
        if(0 == term_id) {
            return;
        }
        
        AttrTable a = get(conn, lang, onto_id, attr_name);
        if(null != a)
            ClassAttrTable.deleteByAttrID(conn, a.attr_id);
        
        try {
            s = conn.createStatement ();
            str_sql = "DELETE FROM attr WHERE onto_id="+onto_id+" AND term_id="+term_id;
            s.execute(str_sql);
            
        } catch(SQLException ex) {
            System.err.println("SQLException (Context.AttrTable.java delete()):: sql='" + str_sql + "' " + ex.getMessage());
        } finally {
            if (rs != null) {   try { rs.close(); } catch (SQLException sqlEx) { }  rs = null; }
            if (s != null)  {   try { s.close();  } catch (SQLException sqlEx) { }  s = null;  }
        }
    }
    
    
}
